package com.example.demo.controller.data;

import lombok.Data;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

@Service
public class ExcelService {

    // 内部类，用于存储渠道统计信息
    @Data
    private static class ChannelStats {
        private int count;
        private double amount;
        private double income;
        public ChannelStats(int count, double amount,double income) {
            this.count = count;
            this.amount = amount;
            this.income = income;
        }
    }
    // 时间段比较器，用于按时间段排序
    private static class TimeRangeComparator implements Comparator<String> {
        @Override
        public int compare(String o1, String o2) {
            // 提取开始时间进行比较
            String startTime1 = o1.split("至")[0];
            String startTime2 = o2.split("至")[0];

            // 尝试解析开始时间为日期对象进行比较
            try {
                // 支持的常见日期时间格式
                List<SimpleDateFormat> dateFormats = new ArrayList<>();
                dateFormats.add(new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"));
                dateFormats.add(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"));
                dateFormats.add(new SimpleDateFormat("yyyy/MM/dd"));
                dateFormats.add(new SimpleDateFormat("yyyy-MM-dd"));

                // 解析第一个时间字符串
                Date date1 = null;
                for (SimpleDateFormat format : dateFormats) {
                    try {
                        date1 = format.parse(startTime1);
                        break;
                    } catch (ParseException ignored) {
                        // 尝试下一个格式
                    }
                }

                // 解析第二个时间字符串
                Date date2 = null;
                for (SimpleDateFormat format : dateFormats) {
                    try {
                        date2 = format.parse(startTime2);
                        break;
                    } catch (ParseException ignored) {
                        // 尝试下一个格式
                    }
                }

                // 如果都能解析，则按日期比较
                if (date1 != null && date2 != null) {
                    return date1.compareTo(date2);
                } else {
                    // 否则按字符串默认比较
                    return startTime1.compareTo(startTime2);
                }
            } catch (Exception e) {
                // 发生异常时按字符串默认比较
                return startTime1.compareTo(startTime2);
            }
        }
    }

    public List<BillDataDto> readExcel(MultipartFile file) throws IOException {
        List<BillDataDto> dataList = new ArrayList<>();
        try (InputStream is = file.getInputStream(); Workbook workbook = new XSSFWorkbook(is)) {
            Sheet sheet = workbook.getSheet("分账明细-正向-团购");
            if (sheet == null) {
                return dataList;
            }

            // 假设第一行是表头
            int firstRowNum = sheet.getFirstRowNum();
            Row headerRow = sheet.getRow(firstRowNum);
            if (headerRow == null) {
                return dataList;
            }

            // 查找列索引
            int nicknameCol = findColumnIndex(headerRow, "核销人昵称");
            int channelCol = findColumnIndex(headerRow, "内容渠道");
            int productCol = findColumnIndex(headerRow, "订单商品");
            int amountCol = findColumnIndex(headerRow, "订单实收金额");
            int timeCol = findColumnIndex(headerRow, "订单支付时间");
            int inComeCol = findColumnIndex(headerRow, "商家应得");
            // 读取数据行
            int lastRowNum = sheet.getLastRowNum();
            for (int i = firstRowNum + 1; i <= lastRowNum; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }

                BillDataDto dto = new BillDataDto();
                dto.setNickname(getCellValue(row.getCell(nicknameCol)));
                dto.setChannel(getCellValue(row.getCell(channelCol)));
                dto.setTime(getCellValue(row.getCell(timeCol)));
                //只取括号外面的内容 channelCol
                if (dto.getChannel().contains("(")) {
                    dto.setChannel(dto.getChannel().substring(0, dto.getChannel().indexOf("(")));
                }
                dto.setInCome(Double.parseDouble(getCellValue(row.getCell(inComeCol))));
                dto.setProduct(getCellValue(row.getCell(productCol)));
                dto.setAmount(Double.parseDouble(getCellValue(row.getCell(amountCol))));

                dataList.add(dto);
            }
        }
        return dataList;
    }

    private int findColumnIndex(Row headerRow, String columnName) {
        for (Cell cell : headerRow) {
            if (columnName.equals(getCellValue(cell))) {
                return cell.getColumnIndex();
            }
        }
        return -1;
    }

    private String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                return String.valueOf(cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }

    private CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        return style;
    }

    private CellStyle createBoldStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        return style;
    }

    private void createCell(Row row, int columnIndex, String value, CellStyle style) {
        Cell cell = row.createCell(columnIndex);
        cell.setCellValue(value);
        cell.setCellStyle(style);
    }

    private void createCell(Row row, int columnIndex, String value) {
        Cell cell = row.createCell(columnIndex);
        cell.setCellValue(value);
    }

    // 提取括号外的渠道名称
    private String extractChannelNameWithoutBrackets(String channelName) {
        if (channelName == null || channelName.isEmpty()) {
            return channelName;
        }
        int startIndex = channelName.indexOf('(');
        if (startIndex > 0) {
            return channelName.substring(0, startIndex).trim();
        }
        return channelName;
    }
    // 添加一个格式化金额的辅助方法
    private String formatAmount(double amount) {
        return String.format("%.2f", amount);
    }
    public byte[] generateAnalysisResult(List<AnalysisResultDto> results) throws IOException {
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        ZipOutputStream zipOut = new ZipOutputStream(baos);
        for (AnalysisResultDto result : results) {
            // 为每个核销人创建Excel文件
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("账单分析");

            // 创建样式
            CellStyle headerStyle = createHeaderStyle(workbook);
            CellStyle boldStyle = createBoldStyle(workbook);
            CellStyle normalStyle = workbook.createCellStyle();
            normalStyle.setBorderTop(BorderStyle.THIN);
            normalStyle.setBorderBottom(BorderStyle.THIN);
            normalStyle.setBorderLeft(BorderStyle.THIN);
            normalStyle.setBorderRight(BorderStyle.THIN);
            // 添加金额单元格的数字格式
            CellStyle amountStyle = workbook.createCellStyle();
            amountStyle.setBorderTop(BorderStyle.THIN);
            amountStyle.setBorderBottom(BorderStyle.THIN);
            amountStyle.setBorderLeft(BorderStyle.THIN);
            amountStyle.setBorderRight(BorderStyle.THIN);
            DataFormat format = workbook.createDataFormat();
            amountStyle.setDataFormat(format.getFormat("0.00"));
            // 设置表头
            Row headerRow = sheet.createRow(0);
            createCell(headerRow, 0, "内容渠道", headerStyle);
            createCell(headerRow, 1, "订单商品", headerStyle);
            createCell(headerRow, 2, "销售单数", headerStyle);
            createCell(headerRow, 3, "销售总金额", headerStyle);
            createCell(headerRow, 4, "商家应得", headerStyle);
            int rowNum = 1;
            // 存储所有行的渠道信息
            List<String> rowChannels = new ArrayList<>();
            // 用于存储每个渠道的统计信息
            Map<String, ChannelStats> channelStatsMap = new HashMap<>();

            // 为每个渠道和产品创建数据行
            for (Map.Entry<String, Map<String, AnalysisResultDto.ChannelProductStats>> channelEntry :
                    result.getChannelProductMap().entrySet()) {

                String channel = channelEntry.getKey();
                // 提取括号外的渠道名称
                String channelNameWithoutBrackets = extractChannelNameWithoutBrackets(channel);

                // 为当前渠道创建或更新统计信息
                ChannelStats stats = channelStatsMap.computeIfAbsent(channelNameWithoutBrackets,
                        k -> new ChannelStats(0, 0.0,0.0));

                // 为每个产品创建行
                for (Map.Entry<String, AnalysisResultDto.ChannelProductStats> productEntry :
                        channelEntry.getValue().entrySet()) {

                    String product = productEntry.getKey();
                    AnalysisResultDto.ChannelProductStats productStats = productEntry.getValue();

                    Row row = sheet.createRow(rowNum);
                    // 先创建空单元格，稍后再处理合并和显示
                    createCell(row, 0, "");
                    createCell(row, 1, product);
                    createCell(row, 2, productStats.getCount().toString());
                    // 修改这里，设置金额为数字类型并应用格式
                    Cell amountCell = row.createCell(3);
                    amountCell.setCellValue(productStats.getAmount());
                    amountCell.setCellStyle(amountStyle);
                    // 修改这里，设置金额为数字类型并应用格式
                    Cell incomeCell = row.createCell(4);
                    incomeCell.setCellValue(productStats.getInCome());
                    incomeCell.setCellStyle(amountStyle);
                    // 设置单元格样式
                    row.getCell(0).setCellStyle(normalStyle);
                    row.getCell(1).setCellStyle(normalStyle);
                    row.getCell(2).setCellStyle(normalStyle);
                    // 记录当前行的渠道信息
                    rowChannels.add(channelNameWithoutBrackets);

                    // 更新渠道统计信息
                    stats.count += productStats.getCount();
                    stats.amount += productStats.getAmount();
                    stats.income += productStats.getInCome();
                    rowNum++;
                }
            }

            // 合并相同渠道的单元格
            if (!rowChannels.isEmpty()) {
                // 创建一个映射，保存每个渠道对应的所有行索引
                Map<String, List<Integer>> channelRowsMap = new HashMap<>();
                for (int i = 0; i < rowChannels.size(); i++) {
                    String channel = rowChannels.get(i);
                    channelRowsMap.computeIfAbsent(channel, k -> new ArrayList<>()).add(i + 1); // +1因为数据从第1行开始
                }

                // 为每个渠道创建合并区域
                for (Map.Entry<String, List<Integer>> entry : channelRowsMap.entrySet()) {
                    List<Integer> rows = entry.getValue();
                    // 只有当一个渠道对应多行时才需要合并
                    if (rows.size() >= 2) {
                        // 对行号进行排序
                        Collections.sort(rows);

                        // 合并所有连续的行块
                        int startRow = rows.get(0);
                        for (int i = 1; i < rows.size(); i++) {
                            // 如果当前行与上一行不连续，创建一个合并区域
                            if (rows.get(i) != rows.get(i - 1) + 1) {
                                CellRangeAddress region = new CellRangeAddress(startRow, rows.get(i - 1), 0, 0);
                                // 设置第一个单元格的值
                                Row firstRowObj = sheet.getRow(startRow);
                                if (firstRowObj != null) {
                                    Cell firstCell = firstRowObj.getCell(0);
                                    if (firstCell != null) {
                                        firstCell.setCellValue(rowChannels.get(startRow - 1));
                                        firstCell.setCellStyle(normalStyle);
                                    }
                                }
                                // 添加合并区域
                                sheet.addMergedRegion(region);
                                startRow = rows.get(i);
                            }
                        }
                        // 处理最后一个连续块
                        if (startRow < rows.get(rows.size() - 1)) {
                            CellRangeAddress region = new CellRangeAddress(startRow, rows.get(rows.size() - 1), 0, 0);
                            // 设置第一个单元格的值
                            Row firstRowObj = sheet.getRow(startRow);
                            if (firstRowObj != null) {
                                Cell firstCell = firstRowObj.getCell(0);
                                if (firstCell != null) {
                                    firstCell.setCellValue(rowChannels.get(startRow - 1));
                                    firstCell.setCellStyle(normalStyle);
                                }
                            }
                            // 添加合并区域
                            sheet.addMergedRegion(region);
                        } else if (startRow == rows.get(rows.size() - 1)) {
                            // 单个行，直接设置值
                            Row row = sheet.getRow(startRow);
                            if (row != null) {
                                Cell cell = row.getCell(0);
                                if (cell != null) {
                                    cell.setCellValue(rowChannels.get(startRow - 1));
                                    cell.setCellStyle(normalStyle);
                                }
                            }
                        }
                    } else if (rows.size() == 1) {
                        // 单个行，直接设置值
                        int rowIndex = rows.get(0);
                        Row row = sheet.getRow(rowIndex);
                        if (row != null) {
                            Cell cell = row.getCell(0);
                            if (cell != null) {
                                cell.setCellValue(rowChannels.get(rowIndex - 1));
                                cell.setCellStyle(normalStyle);
                            }
                        }
                    }
                }
            }

            // 添加每个渠道的小计
            for (Map.Entry<String, ChannelStats> entry : channelStatsMap.entrySet()) {
                String channelName = entry.getKey();
                ChannelStats stats = entry.getValue();

                Row subtotalRow = sheet.createRow(rowNum++);
                createCell(subtotalRow, 0, channelName + " 小计");
                createCell(subtotalRow, 2, String.valueOf(stats.count));

                // 修改这里，设置小计金额为数字类型并应用格式
                Cell subtotalAmountCell = subtotalRow.createCell(3);
                subtotalAmountCell.setCellValue(stats.amount);
                subtotalAmountCell.setCellStyle(boldStyle);
                // 修改这里，设置小计金额为数字类型并应用格式
                Cell incomeAmountCell = subtotalRow.createCell(4);
                incomeAmountCell.setCellValue(stats.income);
                incomeAmountCell.setCellStyle(boldStyle);
                // 设置小计行样式
                for (int i = 0; i <= 3; i++) {
                    if (subtotalRow.getCell(i) != null) {
                        subtotalRow.getCell(i).setCellStyle(boldStyle);
                    }
                }
            }

            // 添加总计行
            Row totalRow = sheet.createRow(rowNum);
            createCell(totalRow, 0, "总计");
            createCell(totalRow, 2, String.valueOf(result.getTotalCount()));
            // 修改这里，设置总计金额为数字类型并应用格式
            Cell totalAmountCell = totalRow.createCell(3);
            totalAmountCell.setCellValue(result.getTotalAmount());
            totalAmountCell.setCellStyle(boldStyle);
            // 修改这里，设置总计金额为数字类型并应用格式
            Cell incomeAmountCell = totalRow.createCell(4);
            incomeAmountCell.setCellValue(result.getTotalIncomeAmount());
            incomeAmountCell.setCellStyle(boldStyle);
            // 设置总计行样式
            for (int i = 0; i <= 3; i++) {
                if (totalRow.getCell(i) != null) {
                    totalRow.getCell(i).setCellStyle(boldStyle);
                }
            }
            // 添加退款分析sheet（如果有退款数据）
            if (result.getRefundChannelProductMap() != null && !result.getRefundChannelProductMap().isEmpty()) {
                addRefundSheet(workbook, result);
            }
            // 设置列宽
            sheet.setColumnWidth(0, 4000);
            sheet.setColumnWidth(1, 12000);
            sheet.setColumnWidth(2, 3000);
            sheet.setColumnWidth(3, 3000);
            sheet.setColumnWidth(4, 3000);
            // 写入到ByteArrayOutputStream
            // 写入ZIP
            ByteArrayOutputStream excelBaos = new ByteArrayOutputStream();
            workbook.write(excelBaos);
            workbook.close();
            zipOut.putNextEntry(new ZipEntry(result.getNickname() + ".xlsx"));
            zipOut.write(excelBaos.toByteArray());
            zipOut.closeEntry();
        }

        return baos.toByteArray();
    }
    public List<RefundDataDto> readRefundExcel(MultipartFile file) throws IOException {
        List<RefundDataDto> dataList = new ArrayList<>();
        try (InputStream is = file.getInputStream(); Workbook workbook = new XSSFWorkbook(is)) {
            Sheet sheet = workbook.getSheet("分账明细-退款-团购");
            if (sheet == null) {
                return dataList;
            }

            // 假设第一行是表头
            int firstRowNum = sheet.getFirstRowNum();
            Row headerRow = sheet.getRow(firstRowNum);
            if (headerRow == null) {
                return dataList;
            }

            // 查找列索引
            int refundTimeCol = findColumnIndex(headerRow, "核销时间");
            int refundTypeCol = findColumnIndex(headerRow, "退款类型");
            int channelCol = findColumnIndex(headerRow, "内容渠道");
            int nicknameCol = findColumnIndex(headerRow, "核销人昵称");
            int productCol = findColumnIndex(headerRow, "订单商品");
            int actualPaymentCol = findColumnIndex(headerRow, "订单实收金额");
            int inComeCol = findColumnIndex(headerRow, "商家应得");
            // 读取数据行
            int lastRowNum = sheet.getLastRowNum();
            for (int i = firstRowNum + 1; i <= lastRowNum; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }

                RefundDataDto dto = new RefundDataDto();
                dto.setRefundTime(getCellValue(row.getCell(refundTimeCol)));
                dto.setRefundType(getCellValue(row.getCell(refundTypeCol)));
                dto.setChannel(getCellValue(row.getCell(channelCol)));
                //只取括号外面的内容
                if (dto.getChannel().contains("(")) {
                    dto.setChannel(dto.getChannel().substring(0, dto.getChannel().indexOf("(")));
                }
                dto.setInCome(Double.parseDouble(getCellValue(row.getCell(inComeCol))));
                dto.setNickname(getCellValue(row.getCell(nicknameCol)));
                dto.setProduct(getCellValue(row.getCell(productCol)));

                // 处理实付金额，可能为空或格式问题
                String paymentStr = getCellValue(row.getCell(actualPaymentCol));
                if (!paymentStr.isEmpty()) {
                    try {
                        dto.setActualPayment(Double.parseDouble(paymentStr));
                    } catch (NumberFormatException e) {
                        dto.setActualPayment(0.0);
                    }
                } else {
                    dto.setActualPayment(0.0);
                }

                dataList.add(dto);
            }
        }
        return dataList;
    }
    public List<RefundDataDto> readRefundExcels(MultipartFile file) throws IOException {
        List<RefundDataDto> dataList = new ArrayList<>();
        try (InputStream is = file.getInputStream(); Workbook workbook = new XSSFWorkbook(is)) {
            Sheet sheet = workbook.getSheet("分账明细-退款-团购");
            if (sheet == null) {
                return dataList;
            }

            // 假设第一行是表头
            int firstRowNum = sheet.getFirstRowNum();
            Row headerRow = sheet.getRow(firstRowNum);
            if (headerRow == null) {
                return dataList;
            }

            // 查找列索引
            int refundTimeCol = findColumnIndex(headerRow, "订单支付时间");
            int refundTypeCol = findColumnIndex(headerRow, "退款类型");
            int channelCol = findColumnIndex(headerRow, "内容渠道");
            int nicknameCol = findColumnIndex(headerRow, "核销人昵称");
            int productCol = findColumnIndex(headerRow, "订单商品");
            int actualPaymentCol = findColumnIndex(headerRow, "订单实收金额");
            int inComeCol = findColumnIndex(headerRow, "商家应得");
            // 读取数据行
            int lastRowNum = sheet.getLastRowNum();
            for (int i = firstRowNum + 1; i <= lastRowNum; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }

                RefundDataDto dto = new RefundDataDto();
                dto.setRefundTime(getCellValue(row.getCell(refundTimeCol)));
                dto.setRefundType(getCellValue(row.getCell(refundTypeCol)));
                dto.setChannel(getCellValue(row.getCell(channelCol)));
                //只取括号外面的内容
                if (dto.getChannel().contains("(")) {
                    dto.setChannel(dto.getChannel().substring(0, dto.getChannel().indexOf("(")));
                }
                dto.setInCome(Double.parseDouble(getCellValue(row.getCell(inComeCol))));
                dto.setNickname(getCellValue(row.getCell(nicknameCol)));
                dto.setProduct(getCellValue(row.getCell(productCol)));

                // 处理实付金额，可能为空或格式问题
                String paymentStr = getCellValue(row.getCell(actualPaymentCol));
                if (!paymentStr.isEmpty()) {
                    try {
                        dto.setActualPayment(Double.parseDouble(paymentStr));
                    } catch (NumberFormatException e) {
                        dto.setActualPayment(0.0);
                    }
                } else {
                    dto.setActualPayment(0.0);
                }

                dataList.add(dto);
            }
        }
        return dataList;
    }
    public void addRefundSheet(Workbook workbook, AnalysisResultDto result) {
        Sheet sheet = workbook.createSheet("退款分析");

        // 创建样式
        CellStyle headerStyle = createHeaderStyle(workbook);
        CellStyle boldStyle = createBoldStyle(workbook);
        CellStyle normalStyle = workbook.createCellStyle();
        normalStyle.setBorderTop(BorderStyle.THIN);
        normalStyle.setBorderBottom(BorderStyle.THIN);
        normalStyle.setBorderLeft(BorderStyle.THIN);
        normalStyle.setBorderRight(BorderStyle.THIN);
        // 添加金额单元格的数字格式
        CellStyle amountStyle = workbook.createCellStyle();
        amountStyle.setBorderTop(BorderStyle.THIN);
        amountStyle.setBorderBottom(BorderStyle.THIN);
        amountStyle.setBorderLeft(BorderStyle.THIN);
        amountStyle.setBorderRight(BorderStyle.THIN);
        DataFormat format = workbook.createDataFormat();
        amountStyle.setDataFormat(format.getFormat("0.00"));

        // 设置表头
        Row headerRow = sheet.createRow(0);
        createCell(headerRow, 0, "内容渠道", headerStyle);
        createCell(headerRow, 1, "订单商品", headerStyle);
        createCell(headerRow, 2, "退款单数", headerStyle);
        createCell(headerRow, 3, "退款总金额", headerStyle);
        createCell(headerRow, 4, "商家应得", headerStyle);
        int rowNum = 1;
        // 存储所有行的渠道信息
        List<String> rowChannels = new ArrayList<>();
        // 用于存储每个渠道的统计信息
        Map<String, ChannelStats> channelStatsMap = new HashMap<>();

        // 为每个渠道和产品创建数据行
        for (Map.Entry<String, Map<String, AnalysisResultDto.ChannelProductStats>> channelEntry :
                result.getRefundChannelProductMap().entrySet()) {

            String channel = channelEntry.getKey();
            // 提取括号外的渠道名称
            String channelNameWithoutBrackets = extractChannelNameWithoutBrackets(channel);

            // 为当前渠道创建或更新统计信息
            ChannelStats stats = channelStatsMap.computeIfAbsent(channelNameWithoutBrackets,
                    k -> new ChannelStats(0, 0.0,0.0));

            // 为每个产品创建行
            for (Map.Entry<String, AnalysisResultDto.ChannelProductStats> productEntry :
                    channelEntry.getValue().entrySet()) {

                String product = productEntry.getKey();
                AnalysisResultDto.ChannelProductStats productStats = productEntry.getValue();

                Row row = sheet.createRow(rowNum);
                // 先创建空单元格，稍后再处理合并和显示
                createCell(row, 0, "");
                createCell(row, 1, product);
                createCell(row, 2, productStats.getCount().toString());
                // 设置金额为数字类型并应用格式
                Cell amountCell = row.createCell(3);
                amountCell.setCellValue(productStats.getAmount());
                amountCell.setCellStyle(amountStyle);
                // 设置金额为数字类型并应用格式
                Cell incomeCell = row.createCell(4);
                incomeCell.setCellValue(productStats.getInCome());
                incomeCell.setCellStyle(amountStyle);
                // 设置单元格样式
                row.getCell(0).setCellStyle(normalStyle);
                row.getCell(1).setCellStyle(normalStyle);
                row.getCell(2).setCellStyle(normalStyle);
                // 记录当前行的渠道信息
                rowChannels.add(channelNameWithoutBrackets);

                // 更新渠道统计信息
                stats.count += productStats.getCount();
                stats.amount += productStats.getAmount();
                stats.income += productStats.getInCome();
                rowNum++;
            }
        }

        // 合并相同渠道的单元格
        if (!rowChannels.isEmpty()) {
            // 创建一个映射，保存每个渠道对应的所有行索引
            Map<String, List<Integer>> channelRowsMap = new HashMap<>();
            for (int i = 0; i < rowChannels.size(); i++) {
                String channel = rowChannels.get(i);
                channelRowsMap.computeIfAbsent(channel, k -> new ArrayList<>()).add(i + 1); // +1因为数据从第1行开始
            }

            // 为每个渠道创建合并区域（与主分析方法相同的逻辑）
            for (Map.Entry<String, List<Integer>> entry : channelRowsMap.entrySet()) {
                List<Integer> rows = entry.getValue();
                // 只有当一个渠道对应多行时才需要合并
                if (rows.size() >= 2) {
                    // 对行号进行排序
                    Collections.sort(rows);

                    // 合并所有连续的行块
                    int startRow = rows.get(0);
                    for (int i = 1; i < rows.size(); i++) {
                        // 如果当前行与上一行不连续，创建一个合并区域
                        if (rows.get(i) != rows.get(i - 1) + 1) {
                            CellRangeAddress region = new CellRangeAddress(startRow, rows.get(i - 1), 0, 0);
                            // 设置第一个单元格的值
                            Row firstRowObj = sheet.getRow(startRow);
                            if (firstRowObj != null) {
                                Cell firstCell = firstRowObj.getCell(0);
                                if (firstCell != null) {
                                    firstCell.setCellValue(rowChannels.get(startRow - 1));
                                    firstCell.setCellStyle(normalStyle);
                                }
                            }
                            // 添加合并区域
                            sheet.addMergedRegion(region);
                            startRow = rows.get(i);
                        }
                    }
                    // 处理最后一个连续块
                    if (startRow < rows.get(rows.size() - 1)) {
                        CellRangeAddress region = new CellRangeAddress(startRow, rows.get(rows.size() - 1), 0, 0);
                        // 设置第一个单元格的值
                        Row firstRowObj = sheet.getRow(startRow);
                        if (firstRowObj != null) {
                            Cell firstCell = firstRowObj.getCell(0);
                            if (firstCell != null) {
                                firstCell.setCellValue(rowChannels.get(startRow - 1));
                                firstCell.setCellStyle(normalStyle);
                            }
                        }
                        // 添加合并区域
                        sheet.addMergedRegion(region);
                    } else if (startRow == rows.get(rows.size() - 1)) {
                        // 单个行，直接设置值
                        Row row = sheet.getRow(startRow);
                        if (row != null) {
                            Cell cell = row.getCell(0);
                            if (cell != null) {
                                cell.setCellValue(rowChannels.get(startRow - 1));
                                cell.setCellStyle(normalStyle);
                            }
                        }
                    }
                } else if (rows.size() == 1) {
                    // 单个行，直接设置值
                    int rowIndex = rows.get(0);
                    Row row = sheet.getRow(rowIndex);
                    if (row != null) {
                        Cell cell = row.getCell(0);
                        if (cell != null) {
                            cell.setCellValue(rowChannels.get(rowIndex - 1));
                            cell.setCellStyle(normalStyle);
                        }
                    }
                }
            }
        }

        // 添加每个渠道的小计
        for (Map.Entry<String, ChannelStats> entry : channelStatsMap.entrySet()) {
            String channelName = entry.getKey();
            ChannelStats stats = entry.getValue();

            Row subtotalRow = sheet.createRow(rowNum++);
            createCell(subtotalRow, 0, channelName + " 小计");
            createCell(subtotalRow, 2, String.valueOf(stats.count));

            // 设置小计金额为数字类型并应用格式
            Cell subtotalAmountCell = subtotalRow.createCell(3);
            subtotalAmountCell.setCellValue(stats.amount);
            subtotalAmountCell.setCellStyle(boldStyle);

            // 设置小计金额为数字类型并应用格式
            Cell incomeAmountCell = subtotalRow.createCell(4);
            incomeAmountCell.setCellValue(stats.income);
            incomeAmountCell.setCellStyle(boldStyle);
            // 设置小计行样式
            for (int i = 0; i <= 3; i++) {
                if (subtotalRow.getCell(i) != null) {
                    subtotalRow.getCell(i).setCellStyle(boldStyle);
                }
            }
        }

        // 添加总计行
        Row totalRow = sheet.createRow(rowNum);
        createCell(totalRow, 0, "总计");
        createCell(totalRow, 2, String.valueOf(result.getTotalRefundCount()));
        // 设置总计金额为数字类型并应用格式
        Cell totalAmountCell = totalRow.createCell(3);
        totalAmountCell.setCellValue(result.getTotalRefundAmount());
        totalAmountCell.setCellStyle(boldStyle);

        Cell incomeCell = totalRow.createCell(4);
        incomeCell.setCellValue(result.getReturnIncomeAmount());
        incomeCell.setCellStyle(boldStyle);

        // 设置总计行样式
        for (int i = 0; i <= 3; i++) {
            if (totalRow.getCell(i) != null) {
                totalRow.getCell(i).setCellStyle(boldStyle);
            }
        }

        // 设置列宽
        sheet.setColumnWidth(0, 4000);
        sheet.setColumnWidth(1, 12000);
        sheet.setColumnWidth(2, 3000);
        sheet.setColumnWidth(3, 3000);
        sheet.setColumnWidth(4, 3000);
    }
    // 读取条件文件
    public List<AnchorConditionDto> readConditionExcel(MultipartFile file) throws IOException {
        List<AnchorConditionDto> conditionList = new ArrayList<>();
        try (InputStream is = file.getInputStream(); Workbook workbook = new XSSFWorkbook(is)) {
            Sheet sheet = workbook.getSheetAt(0); // 读取第一个sheet
            if (sheet == null) {
                return conditionList;
            }

            // 假设第一行是表头
            int firstRowNum = sheet.getFirstRowNum();
            Row headerRow = sheet.getRow(firstRowNum);
            if (headerRow == null) {
                return conditionList;
            }

            // 查找列索引（这里假设顺序是开始时间、结束时间、主播昵称、内容渠道）
            // 实际实现时应该根据表头名称查找列索引
            int startTimeCol = 0; // 第一列
            int endTimeCol = 1;   // 第二列
            int nicknameCol = 2;  // 第三列
            int channelCol = 3;   // 第四列

            // 读取数据行
            int lastRowNum = sheet.getLastRowNum();
            for (int i = firstRowNum + 1; i <= lastRowNum; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                if(getCellValue(row.getCell(nicknameCol)).equals("")){
                    continue;
                }
                AnchorConditionDto dto = new AnchorConditionDto();
                dto.setStartTime(getCellValue(row.getCell(startTimeCol)));
                dto.setEndTime(getCellValue(row.getCell(endTimeCol)));
                dto.setNickname(getCellValue(row.getCell(nicknameCol)));
                dto.setChannel(getCellValue(row.getCell(channelCol)));

                conditionList.add(dto);
            }
        }
        return conditionList;
    }

// 辅助方法：检查时间是否在范围内
    private boolean isInTimeRange(String time, String startTime, String endTime) {
        // 如果时间字符串为空，直接返回false
        if (time == null || time.trim().isEmpty()) {
            return false;
        }

        try {
            // 支持的常见日期时间格式
            List<SimpleDateFormat> dateFormats = new ArrayList<>();
            dateFormats.add(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"));
            dateFormats.add(new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"));
            dateFormats.add(new SimpleDateFormat("yyyy-MM-dd"));
            dateFormats.add(new SimpleDateFormat("yyyy/MM/dd"));

            // 解析时间字符串
            Date targetDate = null;
            for (SimpleDateFormat format : dateFormats) {
                try {
                    targetDate = format.parse(time);
                    break;
                } catch (ParseException ignored) {
                    // 尝试下一个格式
                }
            }

            // 如果无法解析时间字符串，返回false
            if (targetDate == null) {
                return false;
            }

            // 如果开始时间不为空，检查是否在开始时间之后
            if (startTime != null && !startTime.trim().isEmpty()) {
                Date startDate = null;
                for (SimpleDateFormat format : dateFormats) {
                    try {
                        startDate = format.parse(startTime);
                        break;
                    } catch (ParseException ignored) {
                        // 尝试下一个格式
                    }
                }

                if (startDate != null && targetDate.before(startDate)) {
                    return false;
                }
            }

            // 如果结束时间不为空，检查是否在结束时间之前
            if (endTime != null && !endTime.trim().isEmpty()) {
                Date endDate = null;
                for (SimpleDateFormat format : dateFormats) {
                    try {
                        endDate = format.parse(endTime);
                        break;
                    } catch (ParseException ignored) {
                        // 尝试下一个格式
                    }
                }

                if (endDate != null && targetDate.after(endDate)) {
                    return false;
                }
            }

            // 通过所有检查，返回true
            return true;
        } catch (Exception e) {
            // 发生异常时返回false
            return false;
        }
    }

    // 辅助方法：从时间字符串中提取日期
    private String extractDateFromTime(String time) {
        // 如果时间字符串为空，直接返回空字符串
        if (time == null || time.trim().isEmpty()) {
            return "";
        }

        try {
            // 支持的常见日期时间格式
            List<SimpleDateFormat> dateFormats = new ArrayList<>();
            dateFormats.add(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"));
            dateFormats.add(new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"));
            dateFormats.add(new SimpleDateFormat("yyyy-MM-dd"));
            dateFormats.add(new SimpleDateFormat("yyyy/MM/dd"));
            dateFormats.add(new SimpleDateFormat("MM-dd-yyyy HH:mm:ss"));
            dateFormats.add(new SimpleDateFormat("MM/dd/yyyy HH:mm:ss"));
            dateFormats.add(new SimpleDateFormat("MM-dd-yyyy"));
            dateFormats.add(new SimpleDateFormat("MM/dd/yyyy"));

            // 尝试解析时间字符串
            Date date = null;
            for (SimpleDateFormat format : dateFormats) {
                try {
                    date = format.parse(time);
                    break;
                } catch (ParseException ignored) {
                    // 尝试下一个格式
                }
            }

            // 如果无法解析，尝试其他可能的格式或直接截取
            if (date == null) {
                // 尝试直接截取前10个字符（假设格式为 yyyy-MM-dd 或类似格式）
                if (time.length() >= 10) {
                    String potentialDate = time.substring(0, 10);
                    // 检查是否包含日期分隔符
                    if (potentialDate.contains("-") || potentialDate.contains("/")) {
                        return potentialDate;
                    }
                }
                // 如果所有尝试都失败，返回原始时间字符串
                return time;
            }

            // 使用标准格式输出日期部分
            SimpleDateFormat outputFormat = new SimpleDateFormat("yyyy-MM-dd");
            return outputFormat.format(date);
        } catch (Exception e) {
            // 发生异常时返回原始时间字符串
            return time;
        }
    }



    // 生成主播数据Excel压缩包
    public byte[] generateAnchorDataExcelZip(List<BillDataDto> billDataList, List<RefundDataDto> refundDataList, List<AnchorConditionDto> conditionList) throws IOException {
        // 1. 初始化销售数据结构
        Map<String, Map<String, Map<String, Map<String, ChannelStats>>>> anchorDataMap = new TreeMap<>();
        Map<String, Map<String, Map<String, ChannelStats>>> anchorTotalMap = new TreeMap<>();

        // 2. 初始化退款数据结构
        Map<String, Map<String, Map<String, Map<String, ChannelStats>>>> anchorRefundDataMap = new TreeMap<>();
        Map<String, Map<String, Map<String, ChannelStats>>> anchorRefundTotalMap = new TreeMap<>();

        // 确保所有条件中的主播都包含在结果中，即使没有销售数据
        for (AnchorConditionDto condition : conditionList) {
            String nickname = condition.getNickname();
            String channel = condition.getChannel();
            String timeRange = condition.getStartTime() + "至" + condition.getEndTime();

            // 初始化销售数据主播、渠道、时间段的嵌套Map结构
            anchorDataMap.putIfAbsent(nickname, new TreeMap<>());
            anchorDataMap.get(nickname).putIfAbsent(channel, new TreeMap<>(new TimeRangeComparator()));
            anchorDataMap.get(nickname).get(channel).putIfAbsent(timeRange, new TreeMap<>());

            // 初始化销售总计数据
            anchorTotalMap.putIfAbsent(nickname, new TreeMap<>());
            anchorTotalMap.get(nickname).putIfAbsent(channel, new TreeMap<>(new TimeRangeComparator()));
            anchorTotalMap.get(nickname).get(channel).put(timeRange, new ChannelStats(0,0.0,0.0));

            // 初始化退款数据主播、渠道、时间段的嵌套Map结构
            anchorRefundDataMap.putIfAbsent(nickname, new TreeMap<>());
            anchorRefundDataMap.get(nickname).putIfAbsent(channel, new TreeMap<>(new TimeRangeComparator()));
            anchorRefundDataMap.get(nickname).get(channel).putIfAbsent(timeRange, new TreeMap<>());

            // 初始化退款总计数据
            anchorRefundTotalMap.putIfAbsent(nickname, new TreeMap<>());
            anchorRefundTotalMap.get(nickname).putIfAbsent(channel, new TreeMap<>(new TimeRangeComparator()));
            anchorRefundTotalMap.get(nickname).get(channel).put(timeRange, new ChannelStats(0,0.0,0.0));
        }

        // 3. 遍历销售账单数据，更新统计信息
        for (BillDataDto data : billDataList) {
            // 查找匹配的条件
            for (AnchorConditionDto condition : conditionList) {
                // 检查条件匹配
                if (data.getChannel() != null && condition.getChannel() != null &&
                        data.getChannel().contains(condition.getChannel()) &&
                        isInTimeRange(data.getTime(), condition.getStartTime(), condition.getEndTime())) {

                    // 提取日期部分
                    String date = extractDateFromTime(data.getTime());
                    String nickname = condition.getNickname();
                    String channel = condition.getChannel();
                    String timeRange = condition.getStartTime() + "至" + condition.getEndTime();
                    String product = data.getProduct();//商品数据
                    // 更新主播数据
                    anchorDataMap.putIfAbsent(nickname, new TreeMap<>());
                    anchorDataMap.get(nickname).putIfAbsent(channel, new TreeMap<>(new TimeRangeComparator()));
                    anchorDataMap.get(nickname).get(channel).putIfAbsent(timeRange, new TreeMap<>());
                    anchorDataMap.get(nickname).get(channel).get(timeRange).putIfAbsent(product, new ChannelStats(0,0.0,0.0));

                    // 更新统计数据
                    ChannelStats stats = anchorDataMap.get(nickname).get(channel).get(timeRange).get(product);
                    stats.count += data.getNumber() != null ? data.getNumber() : 1;
                    stats.amount += data.getAmount() != null ? data.getAmount() : 0;
                    stats.income += data.getInCome() != null ? data.getInCome() : 0;

                    // 更新总计数据
                    anchorTotalMap.putIfAbsent(nickname, new TreeMap<>());
                    anchorTotalMap.get(nickname).putIfAbsent(channel, new TreeMap<>(new TimeRangeComparator()));
                    anchorTotalMap.get(nickname).get(channel).putIfAbsent(timeRange, new ChannelStats(0,0.0,0.0));

                    ChannelStats totalStats = anchorTotalMap.get(nickname).get(channel).get(timeRange);
                    totalStats.count += data.getNumber() != null ? data.getNumber() : 1;
                    totalStats.amount += data.getAmount() != null ? data.getAmount() : 0;
                    totalStats.income += data.getInCome() != null ? data.getInCome() : 0;
                }
            }
        }

        // 4. 遍历退款数据，更新退款统计信息
        for (RefundDataDto refund : refundDataList) {
            // 查找匹配的条件
            for (AnchorConditionDto condition : conditionList) {
                // 检查条件匹配
                if (refund.getChannel() != null && condition.getChannel() != null &&
                        refund.getChannel().contains(condition.getChannel()) &&
                        isInTimeRange(refund.getRefundTime(), condition.getStartTime(), condition.getEndTime())) {

                    // 提取日期部分
                    String date = extractDateFromTime(refund.getRefundTime());
                    String nickname = condition.getNickname();
                    String channel = condition.getChannel();
                    String timeRange = condition.getStartTime() + "至" + condition.getEndTime();
                    String product = refund.getProduct();//商品数据
                    // 更新退款主播数据
                    anchorRefundDataMap.putIfAbsent(nickname, new TreeMap<>());
                    anchorRefundDataMap.get(nickname).putIfAbsent(channel, new TreeMap<>(new TimeRangeComparator()));
                    anchorRefundDataMap.get(nickname).get(channel).putIfAbsent(timeRange, new TreeMap<>());
                    anchorRefundDataMap.get(nickname).get(channel).get(timeRange).putIfAbsent(product, new ChannelStats(0,0.0,0.0));

                    // 更新退款统计数据
                    ChannelStats stats = anchorRefundDataMap.get(nickname).get(channel).get(timeRange).get(product);
                    stats.count += 1;
                    stats.amount += refund.getActualPayment() != null ? refund.getActualPayment() : 0;
                    stats.income += refund.getInCome() != null ? refund.getInCome() : 0;

                    // 更新退款总计数据
                    anchorRefundTotalMap.putIfAbsent(nickname, new TreeMap<>());
                    anchorRefundTotalMap.get(nickname).putIfAbsent(channel, new TreeMap<>(new TimeRangeComparator()));
                    anchorRefundTotalMap.get(nickname).get(channel).putIfAbsent(timeRange, new ChannelStats(0,0.0,0.0));

                    ChannelStats totalStats = anchorRefundTotalMap.get(nickname).get(channel).get(timeRange);
                    totalStats.count += 1;
                    totalStats.amount += refund.getActualPayment() != null ? refund.getActualPayment() : 0;
                    totalStats.income += refund.getInCome() != null ? refund.getInCome() : 0;
                }
            }
        }

        // 5. 为每个主播生成Excel文件并打包为ZIP
        ByteArrayOutputStream zipBaos = new ByteArrayOutputStream();
        try (ZipOutputStream zos = new ZipOutputStream(zipBaos)) {
            // 遍历每个主播
            for (Map.Entry<String, Map<String, Map<String, Map<String, ChannelStats>>>> anchorEntry : anchorDataMap.entrySet()) {
                String nickname = anchorEntry.getKey();

                // 为当前主播创建Excel文件，包含销售数据和退款数据
                byte[] excelBytes = generateSingleAnchorExcel(nickname,
                        anchorEntry.getValue(),
                        anchorTotalMap.get(nickname),
                        anchorRefundDataMap.get(nickname),
                        anchorRefundTotalMap.get(nickname));

                // 添加到ZIP文件中
                ZipEntry zipEntry = new ZipEntry(nickname + "_销售数据.xlsx");
                zos.putNextEntry(zipEntry);
                zos.write(excelBytes);
                zos.closeEntry();
            }
        }

        return zipBaos.toByteArray();
    }

    // 为单个主播生成Excel文件，包含销售数据和退款数据
    private byte[] generateSingleAnchorExcel(String nickname,
                                             Map<String, Map<String, Map<String, ChannelStats>>> channelDataMap,
                                             Map<String, Map<String, ChannelStats>> totalDataMap,
                                             Map<String, Map<String, Map<String, ChannelStats>>> channelRefundDataMap,
                                             Map<String, Map<String, ChannelStats>> refundTotalDataMap) throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook();

        // 创建销售数据sheet
        createSalesDataSheet(workbook, nickname, channelDataMap, totalDataMap);

        // 创建退款数据sheet
        createRefundDataSheet(workbook, nickname, channelRefundDataMap, refundTotalDataMap);

        // 写入到ByteArrayOutputStream
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);
        workbook.close();

        return baos.toByteArray();
    }

    // 创建销售数据sheet
    private void createSalesDataSheet(XSSFWorkbook workbook, String nickname,
                                      Map<String, Map<String, Map<String, ChannelStats>>> channelDataMap,
                                      Map<String, Map<String, ChannelStats>> totalDataMap) {
        XSSFSheet sheet = workbook.createSheet("销售数据");

        // 创建样式
        XSSFCellStyle headerStyle = workbook.createCellStyle();
        XSSFFont headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);

        XSSFCellStyle normalStyle = workbook.createCellStyle();
        normalStyle.setBorderBottom(BorderStyle.THIN);
        normalStyle.setBorderLeft(BorderStyle.THIN);
        normalStyle.setBorderRight(BorderStyle.THIN);
        normalStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        normalStyle.setAlignment(HorizontalAlignment.CENTER);

        XSSFCellStyle amountStyle = workbook.createCellStyle();
        amountStyle.setBorderBottom(BorderStyle.THIN);
        amountStyle.setBorderLeft(BorderStyle.THIN);
        amountStyle.setBorderRight(BorderStyle.THIN);
        amountStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        amountStyle.setAlignment(HorizontalAlignment.CENTER);
        DataFormat format = workbook.createDataFormat();
        amountStyle.setDataFormat(format.getFormat("0.00"));

        // 设置表头，增加汇总商家应得列
        Row headerRow = sheet.createRow(0);
        createCell(headerRow, 0, "主播昵称", headerStyle);
        createCell(headerRow, 1, "内容渠道", headerStyle);
        createCell(headerRow, 2, "时间段", headerStyle);
        createCell(headerRow, 3, "商品名称", headerStyle);
        createCell(headerRow, 4, "销售数量", headerStyle);
        createCell(headerRow, 5, "销售金额", headerStyle);
        createCell(headerRow, 6, "总单数", headerStyle);
        createCell(headerRow, 7, "总金额", headerStyle);
        createCell(headerRow, 8, "汇总商家应得", headerStyle); // 新增列

        int rowNum = 1;
        String prevChannel = null;
        String prevTimeRange = null;
        int channelStartRow = -1;
        int timeRangeStartRow = -1;

        // 填充数据
        for (Map.Entry<String, Map<String, Map<String, ChannelStats>>> channelEntry : channelDataMap.entrySet()) {
            String channel = channelEntry.getKey();

            for (Map.Entry<String, Map<String, ChannelStats>> timeRangeEntry : channelEntry.getValue().entrySet()) {
                String timeRange = timeRangeEntry.getKey();

                // 记录当前渠道、时间段的开始行
                channelStartRow = rowNum;
                timeRangeStartRow = rowNum;

                // 获取当前分组的总计数据
                ChannelStats totalStats = null;
                if (totalDataMap != null && totalDataMap.containsKey(channel) && totalDataMap.get(channel).containsKey(timeRange)) {
                    totalStats = totalDataMap.get(channel).get(timeRange);
                }

                // 如果没有销售数据，创建一行空数据
                if (timeRangeEntry.getValue().isEmpty()) {
                    Row row = sheet.createRow(rowNum++);

                    // 设置昵称、渠道和时间段的值
                    createCell(row, 0, nickname, normalStyle);
                    createCell(row, 1, channel, normalStyle);
                    createCell(row, 2, timeRange, normalStyle);

                    // 其他列为空或0
                    createCell(row, 3, "无销售数据", normalStyle);
                    createCell(row, 4, "0", normalStyle);

                    Cell amountCell = row.createCell(5);
                    amountCell.setCellValue(0);
                    amountCell.setCellStyle(amountStyle);

                    // 设置总单数和总金额
                    if (totalStats != null) {
                        createCell(row, 6, String.valueOf(totalStats.count), normalStyle);
                        Cell totalAmountCell = row.createCell(7);
                        totalAmountCell.setCellValue(totalStats.amount);
                        totalAmountCell.setCellStyle(amountStyle);
                        // 设置汇总商家应得
                        Cell incomeCell = row.createCell(8);
                        incomeCell.setCellValue(totalStats.income);
                        incomeCell.setCellStyle(amountStyle);
                    } else {
                        createCell(row, 6, "0", normalStyle);
                        Cell emptyTotalAmountCell = row.createCell(7);
                        emptyTotalAmountCell.setCellValue(0);
                        emptyTotalAmountCell.setCellStyle(amountStyle);
                        // 设置汇总商家应得为0
                        Cell emptyIncomeCell = row.createCell(8);
                        emptyIncomeCell.setCellValue(0);
                        emptyIncomeCell.setCellStyle(amountStyle);
                    }
                } else {
                    // 有销售数据的情况，处理现有逻辑
                    for (Map.Entry<String, ChannelStats> dateEntry : timeRangeEntry.getValue().entrySet()) {
                        String date = dateEntry.getKey();
                        ChannelStats stats = dateEntry.getValue();

                        Row row = sheet.createRow(rowNum++);

                        // 确保在每个分组的第一行（即开始行）设置昵称、渠道和时间段的值
                        if (rowNum - 1 == channelStartRow) {
                            // 确保在第一行设置值
                            createCell(row, 0, nickname, normalStyle);
                            createCell(row, 1, channel, normalStyle);
                            createCell(row, 2, timeRange, normalStyle);
                        } else {
                            // 为其他行也创建单元格，避免单元格合并后显示为空
                            createCell(row, 0, "", normalStyle);
                            createCell(row, 1, "", normalStyle);
                            createCell(row, 2, "", normalStyle);
                        }

                        // 修复问题：将日期改为更合适的商品名称描述
                        createCell(row, 3, date, normalStyle);
                        createCell(row, 4, String.valueOf(stats.count), normalStyle);

                        // 设置金额为数字类型并应用格式
                        Cell amountCell = row.createCell(5);
                        amountCell.setCellValue(stats.amount);
                        amountCell.setCellStyle(amountStyle);

                        // 在每个分组的第一行设置总单数、总金额和汇总商家应得
                        if (rowNum - 1 == channelStartRow && totalStats != null) {
                            createCell(row, 6, String.valueOf(totalStats.count), normalStyle);
                            Cell totalAmountCell = row.createCell(7);
                            totalAmountCell.setCellValue(totalStats.amount);
                            totalAmountCell.setCellStyle(amountStyle);
                            // 设置汇总商家应得
                            Cell incomeCell = row.createCell(8);
                            incomeCell.setCellValue(totalStats.income);
                            incomeCell.setCellStyle(amountStyle);
                        } else {
                            // 为其他行的总单数和总金额列也创建单元格
                            createCell(row, 6, "", normalStyle);
                            Cell emptyTotalAmountCell = row.createCell(7);
                            emptyTotalAmountCell.setCellValue(0);
                            emptyTotalAmountCell.setCellStyle(amountStyle);
                            // 为其他行的汇总商家应得列也创建单元格
                            Cell emptyIncomeCell = row.createCell(8);
                            emptyIncomeCell.setCellValue(0);
                            emptyIncomeCell.setCellStyle(amountStyle);
                        }
                    }
                }

                // 合并当前时间段内的相同渠道单元格
                if (timeRangeStartRow < rowNum - 1) {
                    sheet.addMergedRegion(new CellRangeAddress(timeRangeStartRow, rowNum - 1, 2, 2));
                    // 合并总单数、总金额和汇总商家应得列
                    sheet.addMergedRegion(new CellRangeAddress(timeRangeStartRow, rowNum - 1, 6, 6));
                    sheet.addMergedRegion(new CellRangeAddress(timeRangeStartRow, rowNum - 1, 7, 7));
                    sheet.addMergedRegion(new CellRangeAddress(timeRangeStartRow, rowNum - 1, 8, 8));
                }

                // 合并当前渠道内的相同昵称单元格
                if (channelStartRow < rowNum - 1) {
                    sheet.addMergedRegion(new CellRangeAddress(channelStartRow, rowNum - 1, 1, 1));
                }
            }
        }

        // 在设置列宽之前，添加汇总行
        if (rowNum > 1) { // 确保有数据行
            // 计算该主播的总计数据
            int totalCount = 0;
            double totalAmount = 0.0;
            double totalIncome = 0.0;

            if (totalDataMap != null) {
                for (Map<String, ChannelStats> channelMap : totalDataMap.values()) {
                    for (ChannelStats stats : channelMap.values()) {
                        totalCount += stats.count;
                        totalAmount += stats.amount;
                        totalIncome += stats.income;
                    }
                }
            }

            // 创建汇总行
            Row summaryRow = sheet.createRow(rowNum++);

            // 创建汇总行的样式（加粗）
            XSSFCellStyle summaryStyle = workbook.createCellStyle();
            summaryStyle.cloneStyleFrom(normalStyle);
            XSSFFont summaryFont = workbook.createFont();
            summaryFont.setBold(true);
            summaryStyle.setFont(summaryFont);

            XSSFCellStyle summaryAmountStyle = workbook.createCellStyle();
            summaryAmountStyle.cloneStyleFrom(amountStyle);
            summaryAmountStyle.setFont(summaryFont);

            // 设置汇总行的值
            createCell(summaryRow, 0, "总计", summaryStyle);
            sheet.addMergedRegion(new CellRangeAddress(summaryRow.getRowNum(), summaryRow.getRowNum(), 0, 5)); // 合并前6列

            createCell(summaryRow, 6, String.valueOf(totalCount), summaryStyle);

            Cell totalAmountCell = summaryRow.createCell(7);
            totalAmountCell.setCellValue(totalAmount);
            totalAmountCell.setCellStyle(summaryAmountStyle);

            Cell totalIncomeCell = summaryRow.createCell(8);
            totalIncomeCell.setCellValue(totalIncome);
            totalIncomeCell.setCellStyle(summaryAmountStyle);
        }

        // 设置列宽，为新添加的列设置宽度
        sheet.setColumnWidth(0, 4000);
        sheet.setColumnWidth(1, 4000);
        sheet.setColumnWidth(2, 12000);
        sheet.setColumnWidth(3, 12000);
        sheet.setColumnWidth(4, 3000);
        sheet.setColumnWidth(5, 3000);
        sheet.setColumnWidth(6, 3000);
        sheet.setColumnWidth(7, 3000);
        sheet.setColumnWidth(8, 3000); // 汇总商家应得列宽
    }

    // 创建退款数据sheet
    private void createRefundDataSheet(XSSFWorkbook workbook, String nickname,
                                       Map<String, Map<String, Map<String, ChannelStats>>> channelRefundDataMap,
                                       Map<String, Map<String, ChannelStats>> refundTotalDataMap) {
        XSSFSheet sheet = workbook.createSheet("退款数据");

        // 创建样式
        XSSFCellStyle headerStyle = workbook.createCellStyle();
        XSSFFont headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);

        XSSFCellStyle normalStyle = workbook.createCellStyle();
        normalStyle.setBorderBottom(BorderStyle.THIN);
        normalStyle.setBorderLeft(BorderStyle.THIN);
        normalStyle.setBorderRight(BorderStyle.THIN);
        normalStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        normalStyle.setAlignment(HorizontalAlignment.CENTER);

        XSSFCellStyle amountStyle = workbook.createCellStyle();
        amountStyle.setBorderBottom(BorderStyle.THIN);
        amountStyle.setBorderLeft(BorderStyle.THIN);
        amountStyle.setBorderRight(BorderStyle.THIN);
        amountStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        amountStyle.setAlignment(HorizontalAlignment.CENTER);
        DataFormat format = workbook.createDataFormat();
        amountStyle.setDataFormat(format.getFormat("0.00"));

        // 设置表头
        Row headerRow = sheet.createRow(0);
        createCell(headerRow, 0, "主播昵称", headerStyle);
        createCell(headerRow, 1, "内容渠道", headerStyle);
        createCell(headerRow, 2, "时间段", headerStyle);
        createCell(headerRow, 3, "订单商品", headerStyle);
        createCell(headerRow, 4, "退款数量", headerStyle);
        createCell(headerRow, 5, "退款金额", headerStyle);
        createCell(headerRow, 6, "总单数", headerStyle);
        createCell(headerRow, 7, "总金额", headerStyle);
        createCell(headerRow, 8, "商家应得", headerStyle);

        int rowNum = 1;
        int channelStartRow = -1;
        int timeRangeStartRow = -1;

        // 填充数据
        if (channelRefundDataMap != null) {
            for (Map.Entry<String, Map<String, Map<String, ChannelStats>>> channelEntry : channelRefundDataMap.entrySet()) {
                String channel = channelEntry.getKey();

                for (Map.Entry<String, Map<String, ChannelStats>> timeRangeEntry : channelEntry.getValue().entrySet()) {
                    String timeRange = timeRangeEntry.getKey();

                    // 记录当前渠道、时间段的开始行
                    channelStartRow = rowNum;
                    timeRangeStartRow = rowNum;

                    // 获取当前分组的总计数据
                    ChannelStats totalStats = null;
                    if (refundTotalDataMap != null && refundTotalDataMap.containsKey(channel) && refundTotalDataMap.get(channel).containsKey(timeRange)) {
                        totalStats = refundTotalDataMap.get(channel).get(timeRange);
                    }

                    // 如果没有退款数据，创建一行空数据
                    if (timeRangeEntry.getValue().isEmpty()) {
                        Row row = sheet.createRow(rowNum++);

                        // 设置昵称、渠道和时间段的值
                        createCell(row, 0, nickname, normalStyle);
                        createCell(row, 1, channel, normalStyle);
                        createCell(row, 2, timeRange, normalStyle);

                        // 其他列为空或0
                        createCell(row, 3, "无退款数据", normalStyle);
                        createCell(row, 4, "0", normalStyle);

                        Cell amountCell = row.createCell(5);
                        amountCell.setCellValue(0);
                        amountCell.setCellStyle(amountStyle);

                        // 设置总单数和总金额
                        if (totalStats != null) {
                            createCell(row, 6, String.valueOf(totalStats.count), normalStyle);
                            Cell totalAmountCell = row.createCell(7);
                            totalAmountCell.setCellValue(totalStats.amount);
                            totalAmountCell.setCellStyle(amountStyle);
                            // 设置商家应得
                            Cell incomeCell = row.createCell(8);
                            incomeCell.setCellValue(totalStats.income);
                            incomeCell.setCellStyle(amountStyle);
                        } else {
                            createCell(row, 6, "0", normalStyle);
                            Cell emptyTotalAmountCell = row.createCell(7);
                            emptyTotalAmountCell.setCellValue(0);
                            emptyTotalAmountCell.setCellStyle(amountStyle);
                            // 设置商家应得为0
                            Cell emptyIncomeCell = row.createCell(8);
                            emptyIncomeCell.setCellValue(0);
                            emptyIncomeCell.setCellStyle(amountStyle);
                        }
                    } else {
                        // 有退款数据的情况
                        for (Map.Entry<String, ChannelStats> dateEntry : timeRangeEntry.getValue().entrySet()) {
                            String date = dateEntry.getKey();
                            ChannelStats stats = dateEntry.getValue();

                            Row row = sheet.createRow(rowNum++);

                            // 确保在每个分组的第一行（即开始行）设置昵称、渠道和时间段的值
                            if (rowNum - 1 == channelStartRow) {
                                // 确保在第一行设置值
                                createCell(row, 0, nickname, normalStyle);
                                createCell(row, 1, channel, normalStyle);
                                createCell(row, 2, timeRange, normalStyle);
                            } else {
                                // 为其他行也创建单元格，避免单元格合并后显示为空
                                createCell(row, 0, "", normalStyle);
                                createCell(row, 1, "", normalStyle);
                                createCell(row, 2, "", normalStyle);
                            }

                            createCell(row, 3, date, normalStyle);
                            createCell(row, 4, String.valueOf(stats.count), normalStyle);

                            // 设置金额为数字类型并应用格式
                            Cell amountCell = row.createCell(5);
                            amountCell.setCellValue(stats.amount);
                            amountCell.setCellStyle(amountStyle);

                            // 在每个分组的第一行设置总单数、总金额和商家应得
                            if (rowNum - 1 == channelStartRow && totalStats != null) {
                                createCell(row, 6, String.valueOf(totalStats.count), normalStyle);
                                Cell totalAmountCell = row.createCell(7);
                                totalAmountCell.setCellValue(totalStats.amount);
                                totalAmountCell.setCellStyle(amountStyle);
                                // 设置商家应得
                                Cell incomeCell = row.createCell(8);
                                incomeCell.setCellValue(totalStats.income);
                                incomeCell.setCellStyle(amountStyle);
                            } else {
                                // 为其他行的总单数和总金额列也创建单元格
                                createCell(row, 6, "", normalStyle);
                                Cell emptyTotalAmountCell = row.createCell(7);
                                emptyTotalAmountCell.setCellValue(0);
                                emptyTotalAmountCell.setCellStyle(amountStyle);
                                // 为其他行的商家应得列也创建单元格
                                Cell emptyIncomeCell = row.createCell(8);
                                emptyIncomeCell.setCellValue(0);
                                emptyIncomeCell.setCellStyle(amountStyle);
                            }
                        }
                    }

                    // 合并当前时间段内的相同渠道单元格
                    if (timeRangeStartRow < rowNum - 1) {
                        sheet.addMergedRegion(new CellRangeAddress(timeRangeStartRow, rowNum - 1, 2, 2));
                        // 合并总单数、总金额和商家应得列
                        sheet.addMergedRegion(new CellRangeAddress(timeRangeStartRow, rowNum - 1, 6, 6));
                        sheet.addMergedRegion(new CellRangeAddress(timeRangeStartRow, rowNum - 1, 7, 7));
                        sheet.addMergedRegion(new CellRangeAddress(timeRangeStartRow, rowNum - 1, 8, 8));
                    }

                    // 合并当前渠道内的相同昵称单元格
                    if (channelStartRow < rowNum - 1) {
                        sheet.addMergedRegion(new CellRangeAddress(channelStartRow, rowNum - 1, 1, 1));
                    }
                }
            }
        }

        // 在设置列宽之前，添加汇总行
        if (rowNum > 1) { // 确保有数据行
            // 计算该主播的退款总计数据
            int totalCount = 0;
            double totalAmount = 0.0;
            double totalIncome = 0.0;

            if (refundTotalDataMap != null) {
                for (Map<String, ChannelStats> channelMap : refundTotalDataMap.values()) {
                    for (ChannelStats stats : channelMap.values()) {
                        totalCount += stats.count;
                        totalAmount += stats.amount;
                        totalIncome += stats.income;
                    }
                }
            }

            // 创建汇总行
            Row summaryRow = sheet.createRow(rowNum++);

            // 创建汇总行的样式（加粗）
            XSSFCellStyle summaryStyle = workbook.createCellStyle();
            summaryStyle.cloneStyleFrom(normalStyle);
            XSSFFont summaryFont = workbook.createFont();
            summaryFont.setBold(true);
            summaryStyle.setFont(summaryFont);

            XSSFCellStyle summaryAmountStyle = workbook.createCellStyle();
            summaryAmountStyle.cloneStyleFrom(amountStyle);
            summaryAmountStyle.setFont(summaryFont);

            // 设置汇总行的值
            createCell(summaryRow, 0, "总计", summaryStyle);
            sheet.addMergedRegion(new CellRangeAddress(summaryRow.getRowNum(), summaryRow.getRowNum(), 0, 6)); // 合并前7列

            createCell(summaryRow, 6, String.valueOf(totalCount), summaryStyle);

            Cell totalAmountCell = summaryRow.createCell(7);
            totalAmountCell.setCellValue(totalAmount);
            totalAmountCell.setCellStyle(summaryAmountStyle);

            Cell totalIncomeCell = summaryRow.createCell(8);
            totalIncomeCell.setCellValue(totalIncome);
            totalIncomeCell.setCellStyle(summaryAmountStyle);
        }

        // 设置列宽
        sheet.setColumnWidth(0, 4000);
        sheet.setColumnWidth(1, 4000);
        sheet.setColumnWidth(2, 12000);
        sheet.setColumnWidth(3, 6000);
        sheet.setColumnWidth(4, 6000);
        sheet.setColumnWidth(5, 3000);
        sheet.setColumnWidth(6, 3000);
        sheet.setColumnWidth(7, 3000);
        sheet.setColumnWidth(8, 3000);
    }


}